package com.futvan.z.framework.common.service;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Service;
import org.springframework.web.servlet.ModelAndView;

import com.futvan.z.framework.common.bean.MenuTree;
import com.futvan.z.framework.common.bean.Result;
import com.futvan.z.framework.common.bean.z_form_table;
import com.futvan.z.framework.common.bean.z_form_table_column;
import com.futvan.z.framework.core.SuperService;
import com.futvan.z.framework.core.z;
import com.futvan.z.framework.util.BeanUtil;
import com.futvan.z.framework.util.DateUtil;
import com.futvan.z.framework.util.JsonUtil;
import com.futvan.z.framework.util.MathUtil;
import com.futvan.z.framework.util.StringUtil;
import com.futvan.z.system.zdb.z_db;
import com.futvan.z.system.zreport.z_report;
import com.futvan.z.system.zreport.z_report_column;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
@Service
public class CommonService extends SuperService{


	/**
	 * 列表页面新增方法
	 * @param view_name
	 * @param bean
	 * @return
	 */
	public ModelAndView add(String view_name,HashMap<String,String> bean,HttpServletRequest request) {
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
		ModelAndView model = new ModelAndView();
		model.setViewName(view_name);
		model.addObject("bean", bean);
		return model;
	}

	/**
	 * 根据字段Id获取Z5列表页面数据
	 * @param columnId
	 * @return
	 * @throws Exception 
	 */
	public ModelAndView getZ5List(HashMap<String,String> bean) throws Exception {
		ModelAndView model = new ModelAndView("common/form/z5list");
		String TableColunmId = bean.get("TableColunmId");
		String ReturnKeyColumnId = bean.get("ReturnKeyColumnId");
		String ReturnValueColumnId = bean.get("ReturnValueColumnId");
		String Z5QueryParameters = bean.get("Z5QueryParameters");
		String isReport = bean.get("isReport");//是否报表页面调用，1为报表页面
		if(z.isNotNull(TableColunmId)) {
			//关联信息
			String z5_table = "";
			String ColumnId = "";
			String z5_key = "";
			String z5_value = "";

			//获取Z5关联信息
			if(!"1".equals(isReport)) {
				z_form_table_column column = z.columns.get(TableColunmId);
				if(column!=null) {
					z5_table = column.getZ5_table();
					ColumnId = column.getColumn_id();
					z5_key = column.getZ5_key();
					z5_value = column.getZ5_value();
				}else {
					z.Exception("未查询到表单【"+TableColunmId+"】字段关联信息");
				}
			}else {
				z_report_column column = z.reportColumns.get(TableColunmId);
				if(column!=null) {
					z5_table = column.getZ5_table();
					ColumnId = column.getColumn_id();
					z5_key = column.getZ5_key();
					z5_value = column.getZ5_value();
				}else {
					z.Exception("未查询到报表【"+TableColunmId+"】字段关联信息");
				}
			}

			if(z.isNotNull(z5_table) && z.isNotNull(ColumnId)) {
				//设置查询条件
				HashMap<String,String> querybean = new HashMap<String, String>();
				querybean.put("tableId", z5_table);
				querybean.put("ColumnId", ColumnId);
				querybean.put("Z5QueryParameters", Z5QueryParameters);

				//判读是否有其它参数  其它参数格式
				String OtherParameters = bean.get("OtherParameters");
				if(z.isNotNull(OtherParameters)) {
					List<HashMap> opList = JsonUtil.jsonToList(OtherParameters, HashMap.class);
					for (HashMap map : opList) {
						querybean.put(String.valueOf(map.get("id")), String.valueOf(map.get("value")));
					}
				}

				//排序参数
				if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
					querybean.put("orderby", bean.get("orderby"));
					querybean.put("orderby_pattern", bean.get("orderby_pattern"));
				}

				//分页参数
				if(z.isNotNull(bean.get("pagenum")) && z.isNotNull(bean.get("rowcount"))) {
					querybean.put("pagenum", bean.get("pagenum"));
					querybean.put("rowcount", bean.get("rowcount"));
				}else {
					querybean.put("pagenum", "1");
					querybean.put("rowcount", z.sp.get("rowcount"));
				}

				//执行查询
				model.addObject("list", selectList(querybean));

				//设置返回bean
				querybean.put("z5_key", z5_key);
				querybean.put("z5_value", z5_value);
				querybean.put("TableColunmId", TableColunmId);
				querybean.put("ReturnKeyColumnId", ReturnKeyColumnId);
				querybean.put("ReturnValueColumnId", ReturnValueColumnId);
				model.addObject("querybean", querybean);
			}else {
				z.Exception("关联信息中【关联表】或【关联字段】为空，无法查询 ");
			}
		}else {
			z.Exception("关联信息为空 ");
		}
		return model;
	}

	public String getSystemMenuTree(String userId,HttpServletRequest request){
		List<MenuTree> mtList = new ArrayList<MenuTree>();
		//获取所有顶级菜单
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("  zm.zid, ");
		sql.append("  zm.name text, ");
		sql.append("  zm.isopen state, ");
		sql.append("  zm.menu_icon iconCls, ");
		sql.append("  zm.url url ");
		sql.append("FROM z_menu zm ");
		//判读，如是档是管理员，添加角色权限过滤
		String sa = z.sp.get("super_user");
		if(!sa.equals(userId)) {
			sql.append("LEFT JOIN z_role_menu zrm ON zm.zid = zrm.menuid ");
			sql.append("LEFT JOIN z_role_user zru ON zrm.pid = zru.pid ");
			sql.append("WHERE (zm.parentid is null or zm.parentid = '') and zm.is_hidden = 0 and zru.userid = '"+userId+"' ");
		}else {
			sql.append("WHERE (zm.parentid is null or zm.parentid = '') and zm.is_hidden = 0  ");
		}
		sql.append("ORDER BY zm.seq ");
		List<HashMap<String,String>> mt_listmap = selectList(sql.toString());
		//遍历所有顶级菜单
		for (HashMap<String, String> mt_map : mt_listmap) {
			MenuTree mt = BeanUtil.MapToBean(mt_map, MenuTree.class);
			//获取子菜单
			getMenuTreeChildren(mt,userId);
			mtList.add(mt);
		}
		String json = JsonUtil.listToJson(mtList);
		return json;
	}

	/**
	 * 获取子菜单
	 * @param mt
	 */
	private void getMenuTreeChildren(MenuTree mt,String userId) {
		//获取子菜单菜单
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT ");
		sql.append("  zm.zid, ");
		sql.append("  zm.name text, ");
		sql.append("  zm.isopen state, ");
		sql.append("  zm.menu_icon iconCls, ");
		sql.append("  zm.url url ");
		sql.append("FROM z_menu zm ");
		//判读，如是档是管理员，添加角色权限过滤
		String sa = z.sp.get("super_user");
		if(!sa.equals(userId)) {
			sql.append("LEFT JOIN z_role_menu zrm ON zm.zid = zrm.menuid ");
			sql.append("LEFT JOIN z_role_user zru ON zrm.pid = zru.pid ");
			sql.append("WHERE zm.parentid = '"+mt.getZid()+"' and zm.is_hidden = 0 and zru.userid = '"+userId+"' ");
		}else {
			sql.append("WHERE zm.parentid = '"+mt.getZid()+"' and zm.is_hidden = 0  ");
		}
		sql.append("ORDER BY zm.seq ");
		List<HashMap<String,String>> mt_listmap = selectList(sql.toString());
		//遍历所有顶级菜单
		for (HashMap<String, String> mt_map : mt_listmap) {
			MenuTree c_mt = BeanUtil.MapToBean(mt_map, MenuTree.class);
			//获取子菜单
			getMenuTreeChildren(c_mt,userId);
			List<MenuTree> childrenMenuTreeList = mt.getChildren();
			if(z.isNull(childrenMenuTreeList)) {
				childrenMenuTreeList = new ArrayList<MenuTree>();
				childrenMenuTreeList.add(c_mt);
				mt.setChildren(childrenMenuTreeList);
			}else {
				mt.getChildren().add(c_mt);
			}
		}
	}



	/**
	 * 共用列表页面查询方法
	 * @param bean
	 * @param string
	 * @return
	 * @throws Exception 
	 */
	public ModelAndView list(HashMap<String,String> bean,String viewName,HttpServletRequest request) throws Exception {
		ModelAndView model = new ModelAndView(viewName);
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);

		if(z.isNull(bean.get("pagenum")) || z.isNull(bean.get("rowcount"))) {
			bean.put("pagenum", "1");

			//获取表的列表默认显示行数
			z_form_table t = z.tables.get(bean.get("tableId"));
			if(z.isNotNull(t) && z.isNotNull(t.getDefault_rowcount())) {
				bean.put("rowcount", t.getDefault_rowcount());
			}else {
				bean.put("rowcount", z.sp.get("rowcount"));
			}
		}
		model.addObject("list", selectList(bean));
		bean.put("queryinfolist", getQueryInfoList(bean));//常用用户该表单查询条件
		model.addObject("bean", bean);
		return model;
	}

	public ModelAndView rlist(HashMap<String,String> bean,String viewName,HttpServletRequest request) throws Exception {
		ModelAndView model = new ModelAndView(viewName);
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
	
		z_report r = z.reports.get(bean.get("zid"));//根据报表ID获取报表相关参数
		if(z.isNotNull(r)) {
			//如果SQL不为空
			if(z.isNotNull(r.getSqlinfo())) {
	
				//执行SQL-获取当前页数据
				model.addObject("list",getRList(bean,r));
	
				//执行SQL-获取分页相关信息
				getRlistCount(bean,r);
	
				bean.put("queryinfolist", getQueryInfoListR(r,bean.get("session_userid")));//常用用户该表单查询条件
	
				//设置返回bean
				model.addObject("bean", bean);
	
	
	
			}else {
				throw new Exception("rlist error sql is null");
			}
		}else {
			throw new Exception("报表参数为空");
		}
		return model;
	}

	/**
	 * 执行SQL select
	 */
	public List<HashMap<String,String>> SelectForSQL(String sql) {
		return selectList(sql);
	}
	
	public <T> T selectBean(String sqlid, Object parameter) {
		return super.selectBean(sqlid, parameter);
	}
	public String selectString(String sql){
		return super.selectString(sql);
	}
	public int selectInt(String sql){
		return super.selectInt(sql);
	}

	/**
	 * 执行SQL update
	 */
	public int UpdateForSQL(String sql) {
		return update(sql);
	}

	/**
	 * 执行SQL insert
	 */
	public int InsertForSQL(String sql) {
		return insert(sql);
	}

	/**
	 * 执行SQL delete
	 */
	public int DeleteForSQL(String sql) {
		return delete(sql);
	}

	/**
	 * 共用查询单条数据
	 * @param bean
	 * @param ViewName
	 * @return
	 * @throws Exception
	 */
	public ModelAndView edit(String ViewName,HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
		ModelAndView model = new ModelAndView(ViewName);
		model.addObject("bean", selectMap(bean));
		model.addObject("detail", selectDetailsMap(bean));
		return model;
	}



	/**
	 * 插入数据
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result insert(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
		return super.insert(bean,request);
	}

	/**
	 * 修改数据 
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public Result update(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
		return super.update(bean,request);
	}

	/**
	 * 删除数据
	 * @param zids
	 * @return
	 * @throws Exception
	 */
	public Result delete(HashMap<String,String> bean,HttpServletRequest request) throws Exception{
		//从Session中获取用户与组织信息保存到Bean中
		GetSessionInfoToBean(bean,request);
		return super.delete(bean);
	}

	/**
	 *	下移或上移
	 * @param bean
	 * @param string
	 * @return
	 * @throws Exception 
	 */
	public ModelAndView MoveDownOrMoveUp(HashMap<String, String> bean) throws Exception {
		if("list".equals(bean.get("PageType"))) {
			ModelAndView model = new ModelAndView("common/form/list");
			String tableId = bean.get("tableId");


			//获取当前记录seq
			String sql1 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("zid")+"' ";
			HashMap bill_1 = selectMap(sql1);

			//获取当前记录的下一条记录  
			String sql2 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("nextzid")+"' ";
			HashMap bill_2 = selectMap(sql2.toString());

			//如果当前bill2为null 表示当前为最后一条记录
			if(bill_2 != null) {
				//执行下移操作
				String update_1 = "UPDATE "+tableId+" SET  seq = "+bill_2.get("seq")+" WHERE zid = '"+bill_1.get("zid")+"'";
				int u1count = update(update_1);
				if(u1count!=1) {
					throw new Exception("error moveDown u1count is not 1");
				}

				String update_2 = "UPDATE "+tableId+" SET  seq = "+bill_1.get("seq")+" WHERE zid = '"+bill_2.get("zid")+"'";
				int u2count = update(update_2);
				if(u2count!=1) {
					throw new Exception("error moveDown u2count is not 1");
				}

			}

			bean.put("checked_zid", bean.get("zid"));
			bean.remove("zid");

			//设置分页条件
			if(!z.isNotNull(bean.get("pagenum")) || !z.isNotNull(bean.get("rowcount"))) {
				bean.put("pagenum", "1");
				bean.put("rowcount", z.sp.get("rowcount"));
			}

			model.addObject("list", selectList(bean));
			model.addObject("bean", bean);
			return model;
		}else if("edit".equals(bean.get("PageType"))) {
			ModelAndView model = new ModelAndView("common/form/edit");
			String tableId = bean.get("tableId");

			//获取当前记录seq
			String sql1 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("zid")+"' ";
			HashMap bill_1 = selectMap(sql1);

			//获取当前记录的下一条记录  
			String sql2 = "SELECT * FROM "+tableId+" WHERE zid = '"+bean.get("nextzid")+"' ";
			HashMap bill_2 = selectMap(sql2.toString());

			//如果当前bill2为null 表示当前为最后一条记录
			if(bill_2 != null) {
				//执行下移操作
				String update_1 = "UPDATE "+tableId+" SET  seq = "+bill_2.get("seq")+" WHERE zid = '"+bill_1.get("zid")+"'";
				int u1count = update(update_1);
				if(u1count!=1) {
					throw new Exception("error moveDown u1count is not 1");
				}

				String update_2 = "UPDATE "+tableId+" SET  seq = "+bill_1.get("seq")+" WHERE zid = '"+bill_2.get("zid")+"'";
				int u2count = update(update_2);
				if(u2count!=1) {
					throw new Exception("error moveDown u2count is not 1");
				}

			}

			bean.put("checked_zid", bean.get("zid"));
			bean.put("checked_TableID", tableId+"_detail_table");
			bean.put("checked_TableTitle", z.tables.get(tableId).getTable_title());

			//设置ZID为主表的ZID
			bean.put("zid", String.valueOf(bill_1.get("pid")));
			//设置tableId为父表tableId
			bean.put("tableId", z.tables.get(tableId).getParent_table_id());


			model.addObject("bean", selectMap(bean));
			model.addObject("detail", selectDetailsMap(bean));
			return model;
		}else {
			throw new Exception("error PageType is null");
		}
	}

	/**
	 * 根据formId获取tableList
	 * @param formId
	 * @return
	 */
	public List<z_form_table> getTableList(String formId) {
		List<z_form_table> tableList = new ArrayList<z_form_table>();
		List<Map<String,String>> tableListMap = selectList("SELECT zft.* FROM z_form zf INNER JOIN z_form_table zft ON zf.zid = zft.pid WHERE zf.form_id = '"+formId+"'");
		for (Map<String, String> tableMap : tableListMap) {
			z_form_table table = BeanUtil.MapToBean(tableMap, z_form_table.class);
			tableList.add(table);
		}
		return tableList;
	}

	/**
	 * 生成导出excel文件流
	 * @param bean
	 * @return byte[]
	 * @throws Exception
	 */
	public byte[] getExportData(HashMap<String,String> bean) throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		WritableWorkbook wwb = Workbook.createWorkbook(out); 
		//根据表名获取表信息
		z_form_table table = z.tables.get(bean.get("tableId"));
		if(table==null) {
			throw new Exception("生成导出excel出错，找不到Table");
		}
		//生成主Sheet
		int sheet_num = 0;
		bean.remove("pagenum");
		bean.remove("rowcount");
		List<HashMap<String,String>> listData = selectList(bean);
		CreateSheet(wwb,table,sheet_num,listData);
		//生成明细表Sheet
		for (int i = 0; i < table.getDetailTable().size(); i++) {
			//获取明细表
			z_form_table dTable = table.getDetailTable().get(i);
			//获取明细表数据
			//获取主表zid,封装成in条件
			HashMap<String,String> dbean = new HashMap<String, String>();
			dbean.put("tableId", dTable.getTable_id());
			dbean.put("otherwhere", CreatePIDS(listData));
			List<HashMap<String,String>> dListData = selectList(dbean);
			CreateSheet(wwb,dTable,sheet_num+1,dListData);
		}
		wwb.write();
		wwb.close();
		return out.toByteArray();
	}

	/**
	 * 生成导出excel文件流
	 * @param bean
	 * @return byte[]
	 * @throws Exception
	 */
	public byte[] getExportDataR(HashMap<String,String> bean) throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		WritableWorkbook wwb = Workbook.createWorkbook(out); 

		//获取报表ID
		String zid = bean.get("zid");
		z_report r = z.reports.get(zid);
		if(!z.isNotNull(r)) {
			throw new Exception("生成导出excel出错，找不到报表参数R");
		}

		//生成主Sheet
		int sheet_num = 0;
		//封装SQL
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM ( ");
		sql.append(r.getSqlinfo());
		sql.append(" ) z_report_select  ");

		//添加过滤条件
		sql.append(" where 1=1 ");
		//添加综合查询条件
		if(!"".equals(bean.get("query_terms")) && bean.get("query_terms")!=null) {
			String query_terms_sql = CreateQueryTerms(bean.get("query_terms"));
			if(!"".equals(query_terms_sql) && query_terms_sql!=null) {
				sql.append(" and "+query_terms_sql+" ");
			}
		}
		//添加其它条件
		if(!"".equals(bean.get("otherwhere")) && bean.get("otherwhere")!=null) {
			sql.append(" and "+bean.get("otherwhere")+" ");
		}

		//添加排序条件
		if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
			//如果参数对象的orderby条件不为空，使用参数对象的条件
			sql.append(" order by "+bean.get("orderby")+" "+bean.get("orderby_pattern"));
		}
		List<HashMap<String,String>> listData = selectList(sql.toString());

		CreateSheetR(wwb,r,sheet_num,listData);
		wwb.write();
		wwb.close();
		return out.toByteArray();
	}

	/**
	 * 生成PID查询条件
	 * @param listData
	 * @return
	 */
	private String CreatePIDS(List<HashMap<String, String>> listData) {
		//String result = " pid in ( ";
		StringBuffer result = new StringBuffer();
		result.append(" pid in ( ");
		if(z.isNotNull(listData)) {
			for (int i = 0; i < listData.size(); i++) {
				HashMap<String,String> hr = listData.get(i);
				if(i==0) {
					result.append("'"+hr.get("zid")+"'");
				}else {
					result.append(",'"+hr.get("zid")+"'");
				}
			}
		}
		result.append(" ) ");
		return result.toString();
	}

	/**
	 * 导出Excel ---  生成sheet页面
	 * @param table
	 * @param sheet_num
	 * @throws Exception
	 */
	private void CreateSheet(WritableWorkbook wwb,z_form_table table,int sheet_num,List<HashMap<String,String>> listData) throws Exception {

		//创建工作表
		wwb.createSheet(table.getTable_title(), sheet_num);

		//获取工作表 
		WritableSheet ws = wwb.getSheet(sheet_num);

		//表头行样式
		WritableCellFormat TableHead = new WritableCellFormat();
		TableHead.setBorder(Border.ALL, BorderLineStyle.THIN);
		TableHead.setAlignment(Alignment.LEFT);
		TableHead.setBackground(Colour.GRAY_25);
		//表体数据行样式
		WritableCellFormat TableRow = new WritableCellFormat();
		TableRow.setAlignment(Alignment.LEFT);

		//获取所有字段
		List<z_form_table_column> columnArray = new ArrayList<z_form_table_column>();
		List<z_form_table_column> tableColumnList = table.getColumns();
		for (int i = 0; i < tableColumnList.size(); i++) {
			z_form_table_column column = tableColumnList.get(i);
			if("0".equals(column.getIs_hidden())) {//判读是否隐藏
				if(!"1".equals(column.getIs_hidden_list())){//判读是否列表隐藏
					columnArray.add(column);
				}
			}

		}
		//添加主键外键
		columnArray.add(z.columns.get(table.getTable_id()+"_zid"));
		if(sheet_num!=0) {
			columnArray.add(z.columns.get(table.getTable_id()+"_pid"));
		}

		//生成表头行
		for (int i = 0; i < columnArray.size(); i++) {
			if(z.isNotNull(columnArray.get(i))){
				z_form_table_column column = columnArray.get(i);
				ws.addCell(new Label(i,0,column.getColumn_name(),TableHead));
			}
		}


		//生成记录行
		if(z.isNotNull(listData)) {
			for (int i = 0; i < listData.size(); i++) {
				HashMap<String,String> hr = listData.get(i);
				for (int j = 0; j < columnArray.size(); j++) {
					if(z.isNotNull(columnArray.get(j))){
						z_form_table_column column = columnArray.get(j);
						if("0".equals(column.getColumn_type())) {//文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("1".equals(column.getColumn_type())) {//多行文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("2".equals(column.getColumn_type())) {//数字
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("3".equals(column.getColumn_type())) {//文件
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("4".equals(column.getColumn_type())) {//图片
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("5".equals(column.getColumn_type())) {//多选
							String CheckedValue = CheckedValue(column,StringUtil.toString(hr.get(column.getColumn_id())));
							ws.addCell(new Label(j,i+1,CheckedValue,TableRow));
						}else if("6".equals(column.getColumn_type())) {//单选
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {
								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("7".equals(column.getColumn_type())) {//下拉框
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {
								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("8".equals(column.getColumn_type())) {//Z5
							String columnValue = StringUtil.toString(hr.get(column.getColumn_id()));
							ws.addCell(new Label(j,i+1,getZ5DisplayValue(column,columnValue),TableRow));
						}else if("9".equals(column.getColumn_type())) {//日期
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd"),TableRow));
						}else if("10".equals(column.getColumn_type())) {//日期时间
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd HH:mm:ss"),TableRow));
						}else if("11".equals(column.getColumn_type())) {//HTML输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("12".equals(column.getColumn_type())) {//源码输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}
					}
				}
			}
		}

	}

	/**
	 * 导出Excel ---  生成sheet页面
	 * @param table
	 * @param sheet_num
	 * @throws Exception
	 */
	private void CreateSheetR(WritableWorkbook wwb,z_report r,int sheet_num,List<HashMap<String,String>> listData) throws Exception {

		//创建工作表
		wwb.createSheet(r.getTitle(), sheet_num);

		//获取工作表 
		WritableSheet ws = wwb.getSheet(sheet_num);

		//表头行样式
		WritableCellFormat TableHead = new WritableCellFormat();
		TableHead.setBorder(Border.ALL, BorderLineStyle.THIN);
		TableHead.setAlignment(Alignment.LEFT);
		TableHead.setBackground(Colour.GRAY_25);
		//表体数据行样式
		WritableCellFormat TableRow = new WritableCellFormat();
		TableRow.setAlignment(Alignment.LEFT);

		//获取所有字段
		List<z_report_column> columnArray = new ArrayList<z_report_column>();
		List<z_report_column> tableColumnList = r.getZ_report_column_list();
		for (int i = 0; i < tableColumnList.size(); i++) {
			z_report_column column = tableColumnList.get(i);
			columnArray.add(column);

		}

		//生成表头行
		for (int i = 0; i < columnArray.size(); i++) {
			if(z.isNotNull(columnArray.get(i))){
				z_report_column column = columnArray.get(i);
				ws.addCell(new Label(i,0,column.getColumn_name(),TableHead));
			}
		}


		//生成记录行
		if(z.isNotNull(listData)) {
			for (int i = 0; i < listData.size(); i++) {
				HashMap<String,String> hr = listData.get(i);
				for (int j = 0; j < columnArray.size(); j++) {
					if(z.isNotNull(columnArray.get(j))){
						z_report_column column = columnArray.get(j);
						if("0".equals(column.getColumn_type())) {//文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("1".equals(column.getColumn_type())) {//多行文本
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("2".equals(column.getColumn_type())) {//数字
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("3".equals(column.getColumn_type())) {//文件
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("4".equals(column.getColumn_type())) {//图片
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("5".equals(column.getColumn_type())) {//多选
							String CheckedValue = CheckedValueR(column,StringUtil.toString(hr.get(column.getColumn_id())));
							ws.addCell(new Label(j,i+1,CheckedValue,TableRow));
						}else if("6".equals(column.getColumn_type())) {//单选
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {

								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("7".equals(column.getColumn_type())) {//下拉框
							String cv = z.codeValue.get(column.getP_code_id()+"_"+hr.get(column.getColumn_id()));
							if("".equals(cv) || cv==null) {
								cv = StringUtil.toString(hr.get(column.getColumn_id()));
							}
							ws.addCell(new Label(j,i+1,StringUtil.toString(cv),TableRow));
						}else if("8".equals(column.getColumn_type())) {//Z5
							String columnValue = StringUtil.toString(hr.get(column.getColumn_id()));
							ws.addCell(new Label(j,i+1,getZ5DisplayValueR(column,columnValue),TableRow));
						}else if("9".equals(column.getColumn_type())) {//日期
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd"),TableRow));
						}else if("10".equals(column.getColumn_type())) {//日期时间
							ws.addCell(new Label(j,i+1,DateUtil.FormatDate(hr.get(column.getColumn_id()), "yyyy-MM-dd HH:mm:ss"),TableRow));
						}else if("11".equals(column.getColumn_type())) {//HTML输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}else if("12".equals(column.getColumn_type())) {//源码输入框
							ws.addCell(new Label(j,i+1,String.valueOf(hr.get(column.getColumn_id())),TableRow));
						}
					}
				}
			}
		}

	}

	/**
	 * 执行SQL-获取分页相关信息
	 * @param bean
	 * @param r
	 */
	private void getRlistCount(HashMap<String, String> bean, z_report r) {
		//封装SQL
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(*) FROM ( ");
		//变量替换 
		sql.append(StringUtil.parseExpression(r.getSqlinfo(), bean)   );
		sql.append(" ) z_report_select  ");

		//添加过滤条件
		sql.append(" where 1=1 ");
		//添加综合查询条件
		if(!"".equals(bean.get("query_terms")) && bean.get("query_terms")!=null) {
			String query_terms_sql = CreateQueryTerms(bean.get("query_terms"));
			if(!"".equals(query_terms_sql) && query_terms_sql!=null) {
				sql.append(" and "+query_terms_sql+" ");
			}
		}
		//添加其它条件
		if(!"".equals(bean.get("otherwhere")) && bean.get("otherwhere")!=null) {
			sql.append(" and "+bean.get("otherwhere")+" ");
		}

		//添加排序条件
		if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
			//如果参数对象的orderby条件不为空，使用参数对象的条件
			sql.append(" order by "+bean.get("orderby")+" "+bean.get("orderby_pattern"));
		}
		//设置返回记录总数
		int datacount = 0;
		if(z.isNull(r.getDbid()) || "z".equals(r.getDbid())) {
			datacount = selectInt(sql.toString());
		}else {
			datacount = selectInt(z.dbs.get(r.getDbid()),sql.toString());
		}

		bean.put("datacount", String.valueOf(datacount));

		//设置总页数
		int rowcount = new Integer(z.sp.get("rowcount"));
		if(z.isNotNull(bean.get("rowcount"))) {
			rowcount = new Integer(bean.get("rowcount"));
		}
		bean.put("pagecount", MathUtil.getPageCount(datacount,rowcount));
	}

	/**
	 * 执行SQL-获取当前页数据
	 * @param bean
	 * @param r
	 * @return
	 */
	private List<HashMap<String,String>> getRList(HashMap<String, String> bean,z_report r) {
		//封装SQL
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM ( ");
		//变量替换 
		sql.append(StringUtil.parseExpression(r.getSqlinfo(), bean)   );
		sql.append(" ) z_report_select  ");

		//添加过滤条件
		sql.append(" where 1=1 ");

		//添加分页【Oracle】
		z_db db = z.dbsMap.get(r.getDbid());
		if(z.isNotNull(db) && "oracle".equals(db.getDb_type())) {
			String pagenum = bean.get("pagenum");
			String rowcount = bean.get("rowcount");
			if(!z.isNotNull(pagenum) || !z.isNotNull(rowcount)) {
				pagenum = "1";
				rowcount = z.sp.get("rowcount");
			}
			int pagenum_int = Integer.valueOf(pagenum);//页号
			int rowcount_int = Integer.valueOf(rowcount);//分页显示行数
			if(pagenum_int>0 && rowcount_int>0) {//页号和分布显示行数都必须大于0
				int row_num = (pagenum_int-1)*rowcount_int;//分页开始行号
				sql.append(" and rownum >= "+row_num+" and rownum<="+rowcount+"  ");
			}else {
				sql.append(" and rownum <= "+rowcount+"  ");
			}
			bean.put("pagenum", pagenum);
			bean.put("rowcount", rowcount);
		}

		//添加综合查询条件
		if(!"".equals(bean.get("query_terms")) && bean.get("query_terms")!=null) {
			String query_terms_sql = CreateQueryTerms(bean.get("query_terms"));
			if(!"".equals(query_terms_sql) && query_terms_sql!=null) {
				sql.append(" and "+query_terms_sql+" ");
			}
		}
		//添加其它条件
		if(!"".equals(bean.get("otherwhere")) && bean.get("otherwhere")!=null) {
			sql.append(" and "+bean.get("otherwhere")+" ");
		}

		//添加排序条件
		if(z.isNotNull(bean.get("orderby")) && z.isNotNull(bean.get("orderby_pattern"))) {
			//如果参数对象的orderby条件不为空，使用参数对象的条件
			sql.append(" order by "+bean.get("orderby")+" "+bean.get("orderby_pattern"));
		}


		//添加分页【MySQL】
		if(z.isNotNull(db) && "mysql".equals(db.getDb_type())) {
			String pagenum = bean.get("pagenum");
			String rowcount = bean.get("rowcount");
			if(!z.isNotNull(pagenum) || !z.isNotNull(rowcount)) {
				pagenum = "1";
				rowcount = z.sp.get("rowcount");
			}
			int pagenum_int = Integer.valueOf(pagenum);//页号
			int rowcount_int = Integer.valueOf(rowcount);//分页显示行数
			if(pagenum_int>0 && rowcount_int>0) {//页号和分布显示行数都必须大于0
				int row_num = (pagenum_int-1)*rowcount_int;//分页开始行号
				sql.append(" limit "+row_num+","+rowcount);
			}else {
				sql.append(" limit 0,"+rowcount);
			}
			bean.put("pagenum", pagenum);
			bean.put("rowcount", rowcount);
		}


		if(z.isNull(r.getDbid()) || "z".equals(r.getDbid())) {
			return selectList(sql.toString());
		}else {
			return selectList(z.dbs.get(r.getDbid()),sql.toString());
		}

	}



}
